import java.awt.Color;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

import com.mysql.jdbc.PreparedStatement;


public class AdminReportsForm extends JPanel{
	private static final long serialVersionUID = 1L;

	private  ImageIcon repH = new ImageIcon(this.getClass().getResource("images/header/report.png"));
	JButton viewARep, viewPRep;
	JLabel repo, atteLab, payLab;
	String viewAtten = "<html><h1>Attendance Report</h1></html>";
	String viewPayro = "<html><h1>Payroll Report</h1></html>";
	private static JComboBox smonth, sday, syear, emonth, eday, eyear;
	private JLabel frlabel, tolabel;
	private static AttRepPane attRepMenu = new AttRepPane();
	private static PaySumPane paySumMenu = new PaySumPane();
	String[] monthItems = { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
	String[] yearItems = { "2011"};
	int[] intDay = new int[31];
	String[] dayItems = new String[31];

	
	
	public AdminReportsForm(){

		repo = new JLabel(repH);
		repo.setBounds(135, 37, repH.getIconWidth(), repH.getIconHeight());
		repo.setVisible(true);

		attRepMenu.setBounds(130, 65, 617, 500);
		paySumMenu.setBounds(130, 65, 617, 500);

		viewARep = new JButton("View Attendance Report");
		viewARep.setBounds (490, 95, 200, 25);
		viewARep.setVisible(true);
		viewARep.setForeground(Color.WHITE);
		viewARep.setBackground(Color.BLACK);

		viewPRep = new JButton("View Payroll Report");
		viewPRep.setBounds(490, 120, 200, 25);
		viewPRep.setVisible(true);
		viewPRep.setForeground(Color.WHITE);
		viewPRep.setBackground(Color.BLACK);

		atteLab = new JLabel(viewAtten);
		atteLab.setBounds(140, 60, 550, 100);
		atteLab.setVisible(false);
		payLab = new JLabel(viewPayro);
		payLab.setVisible(false);
		payLab.setBounds(140, 60, 550, 100);

		frlabel = new JLabel ("from:");
		smonth = new JComboBox(monthItems);
		sday = new JComboBox();
		syear = new JComboBox(yearItems);
		frlabel.setBounds (140, 400, 30, 25);
		smonth.setBounds(170, 400, 100, 25);
		sday.setBounds(270, 400, 60, 25);
		syear.setBounds(330, 400, 95, 25);

		tolabel = new JLabel ("to:");
		emonth = new JComboBox(monthItems);
		eday = new JComboBox();
		eyear = new JComboBox(yearItems);
		tolabel.setBounds (430, 400, 30, 25);
		emonth.setBounds(460, 400, 100, 25);
		eday.setBounds(560, 400, 60, 25);
		eyear.setBounds(620, 400, 95, 25);

		InitializeDrop(new Integer(31));
        InitializeDropE(new Integer(31));
		
        add (frlabel);
		add (smonth);
		add (sday);
		add (syear);
		add (tolabel);
		add (emonth);
		add (eday);
		add (eyear);

		add(paySumMenu);
		add(attRepMenu);
		add(repo);
		add(viewARep);
		add(viewPRep);
		add(atteLab);
		add(payLab);
		setLayout(null);
		setOpaque(false);
		setSize(806,628);
		setVisible(true);

		viewARep.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				payLab.setVisible(false);
				atteLab.setVisible(true);
				attRepMenu.attendUpdate();
				attRepMenu.setVisible(true);
				paySumMenu.setVisible(false);
			}
		});


		viewPRep.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				atteLab.setVisible(false);
				payLab.setVisible(true);
				paySumMenu.payrollUpdate();
				attRepMenu.setVisible(false);
				paySumMenu.setVisible(true);
			}
		});
		
		smonth.addActionListener(new ActionListener() {
            public void actionPerformed (ActionEvent e){
                
               boolean leap = false;
               
               leap = CheckLeap(Integer.parseInt(syear.getSelectedItem().toString()));
               
               if(smonth.getSelectedIndex() == 1)
               {
                   if(leap==false)
                       InitializeDrop(28);
                   else
                       InitializeDrop(29); 
               }
               else if (smonth.getSelectedIndex() == 0 || smonth.getSelectedIndex() == 2 || smonth.getSelectedIndex() == 4 
                       || smonth.getSelectedIndex() == 6 || smonth.getSelectedIndex() == 7 || smonth.getSelectedIndex() == 9
                       || smonth.getSelectedIndex() == 11)
                   InitializeDrop(31);
               else if (smonth.getSelectedIndex() == 3 || smonth.getSelectedIndex() == 5 || smonth.getSelectedIndex() == 8
                       || smonth.getSelectedIndex() == 10)
                   InitializeDrop(30);
            }
        }
        );

		emonth.addActionListener(new ActionListener() {
            public void actionPerformed (ActionEvent e){
                
               boolean leap = false;
               
               leap = CheckLeap(Integer.parseInt(eyear.getSelectedItem().toString()));
               
               if(emonth.getSelectedIndex() == 1)
               {
                   if(leap==false)
                       InitializeDropE(28);
                   else
                       InitializeDropE(29); 
               }
               else if (emonth.getSelectedIndex() == 0 || emonth.getSelectedIndex() == 2 || emonth.getSelectedIndex() == 4 
                       || emonth.getSelectedIndex() == 6 || emonth.getSelectedIndex() == 7 || emonth.getSelectedIndex() == 9
                       || emonth.getSelectedIndex() == 11)
                   InitializeDropE(31);
               else if (emonth.getSelectedIndex() == 3 || emonth.getSelectedIndex() == 5 || emonth.getSelectedIndex() == 8
                       || emonth.getSelectedIndex() == 10)
                   InitializeDropE(30);
            }
        }
        );
	}
	

	public static int getSmonth() {
		String mon = smonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public static String getSday() {
		return sday.getSelectedItem().toString();
	}

	public static String getSyear() {
		return syear.getSelectedItem().toString();
	}

	public static int getEmonth() {
		String mon = emonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public static String getEday() {
		return eday.getSelectedItem().toString();
	}

	public static String getEyear() {
		return eyear.getSelectedItem().toString();
	}


	public void InitializeDrop(int ctr) {
      
        sday.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
        	 	intDay[a-1] = a;
        	 	dayItems[a-1] = Integer.toString(intDay[a-1]);
        	 	sday.addItem(dayItems[a-1]);
            }
         
         sday.repaint();
       
            
    }
	public void InitializeDropE(int ctr) {
	      
        eday.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
        	 	intDay[a-1] = a;
        	 	dayItems[a-1] = Integer.toString(intDay[a-1]);
        	 	eday.addItem(dayItems[a-1]);
            }
         
         eday.repaint();
       
            
    }	
	public boolean CheckLeap(int year) {
        if(year%4 == 0)
        {
            if(year%100 != 0)
            {
                return true;
            }
            else
            {
                if(year%400 == 0)
                    return true;
                else
                    return false;
            }
        }
        return false;
        }
	
	
	public  AttRepPane getAPane(){
		return attRepMenu;
	}
	public  PaySumPane getPPane(){
		return paySumMenu;
	}
}

class AttRepPane extends JPanel{
	private static final long serialVersionUID = 1L;
	private JTable tableMod;
	private DefaultTableModel attendList;

	Connection conn = null;

	public AttRepPane() {    



		//construction of table Attendance Report
		String[] columnNames = {"Employee Name", "Date", "Time In", "Time Out", "Status", "Work Hours"};
		tableMod = new JTable();
		tableMod.setModel(new javax.swing.table.DefaultTableModel(null, columnNames){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int rowIndex, int columnIndex) {
				return false;
			}
		});
		tableMod.setFillsViewportHeight(true);
		tableMod.getTableHeader().setReorderingAllowed(false);
		attendList = (DefaultTableModel)tableMod.getModel();
		JScrollPane	scrollPane = new JScrollPane();
		scrollPane.setViewportView(tableMod);

		//adjust size and set layout
		setPreferredSize (new Dimension (617, 500));
		setLayout (null);
		setOpaque(false);
		setSize(617,437);
		setVisible(false);


		//add components
		add (scrollPane);

		//set component bounds (only needed by Absolute Positioning)
		scrollPane.setBounds(20, 85, 590, 250);
	}

	public void attendUpdate(){

		try
		{
			ConnectionFactory myFactory = ConnectionFactory.getFactory();
			Connection conn = myFactory.getConnection();
			PreparedStatement stat = (PreparedStatement) conn.prepareStatement("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name',"+
					"timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
			"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.idemployee = e.idemployee and s.aStat = a.stat and a.dateSal >= '"+AdminReportsForm.getSyear()+"-"+AdminReportsForm.getSmonth()+"-"+AdminReportsForm.getSday()+"' and a.dateSal <= '"+
					AdminReportsForm.getEyear()+"-"+AdminReportsForm.getEmonth()+"-"+AdminReportsForm.getEday()+"'"); 
			ResultSet res = stat.executeQuery();

			attendList.getDataVector().removeAllElements();
			tableMod.setAutoCreateRowSorter(true);

			while(res.next()){
				attendList.addRow(new Object[]{ res.getString("Full Name"), res.getDate("Date"), res.getTime("Time-In"), res.getTime("Time-Out"), res.getString("Status"), res.getInt("Work Hours")});
				attendList.fireTableDataChanged();
			}

			conn.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}

	}
}

class PaySumPane extends JPanel{
	private static final long serialVersionUID = 1L;

	JLabel	numEmp, aveHours, numDays, totDed, totBon, totSal, totCost;

	private JTable tableMod;
	private DefaultTableModel payrollList;

	Connection conn = null;


	public PaySumPane() {    

		numEmp = new JLabel("Number of Employees: ");
		aveHours = new JLabel("Average Working Hours: ");
		numDays = new JLabel("Number of Working Days: ");
		totDed = new JLabel("Total Deductions: ");
		totBon = new JLabel("Total Bonuses: ");
		totSal = new JLabel("Total Salary: ");
		totCost = new JLabel("Total Cost: ");

		//construction of table PayrollSummary
		String[] columnNames = {"Employee Name", "Work Hours", "Total Bonus", "Total Deduction", "Total Salary", "Total Employee Cost"};
		tableMod = new JTable();
		tableMod.setModel(new javax.swing.table.DefaultTableModel(null, columnNames){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int rowIndex, int columnIndex) {
				return false;
			}
		});
		tableMod.setFillsViewportHeight(true);
		tableMod.getTableHeader().setReorderingAllowed(false);
		payrollList = (DefaultTableModel)tableMod.getModel();
		JScrollPane	scrollPane = new JScrollPane();
		scrollPane.setViewportView(tableMod);

		//adjust size and set layout
		setLayout (null);
		setOpaque(false);
		setSize(617,500);
		setVisible(false);

		//add components
		add (scrollPane);
		add(numEmp);
		add(aveHours);
		add(numDays);
		add(totDed);
		add(totBon);
		add(totSal);
		add(totCost);

		//set component bounds (only needed by Absolute Positioning)
		scrollPane.setBounds(20, 85, 590, 250);
		numEmp.setBounds(30, 360, 250, 25);
		aveHours.setBounds(30, 390, 250, 25);
		numDays.setBounds(30, 420, 250, 25);
		totDed.setBounds(330, 360, 250, 25);
		totBon.setBounds(330, 390, 250, 25);
		totSal.setBounds(330, 420, 250, 25);
		totCost.setBounds(330,450, 250, 25);


		//set text of payroll summary label
		numEmp.setText("Number of Employees: " + "numEmp");
		aveHours.setText("Average Working Hours: " + "ave");
		numDays.setText("Number of Working Days: " + "numDays");
		totDed.setText("Total Deductions: " + "deduc");
		totBon.setText("Total Bonuses: " + "bonus");
		totSal.setText("Total Salary: " + "sal");
		totCost.setText("Total Cost: " + "cost");

	}

	public void payrollUpdate(){
		float totalSal = 0;
		float totalCost = 0;
		float totalDed = 0;
		float totalBon = 0;
		float totalEmp = 0;
		try
		{
			ConnectionFactory myFactory = ConnectionFactory.getFactory();
			Connection conn = myFactory.getConnection();
			PreparedStatement stat = (PreparedStatement) conn.prepareStatement("SELECT	t1.Bonus as 'Bonus', t2.Deduction as 'Deduction', t3.WorkHours as 'Work Hours', t4.baseSalary as 'Base Salary', t4.Name as 'Full Name' from"+
					"(SELECT  idpayroll, ROUND(SUM(amount),2) as 'Bonus' from bernalcompany.deductionbonus p where p.dateSal >= '"+AdminReportsForm.getSyear()+"-"+AdminReportsForm.getSmonth()+"-"+AdminReportsForm.getSday()+"' and p.dateSal <= '"+AdminReportsForm.getEyear()+"-"+AdminReportsForm.getEmonth()+"-"+AdminReportsForm.getEday()+"' and ptype = 0 group by idpayroll) as t1,"+
					"(SELECT  idpayroll, ROUND(SUM(amount),2) as 'Deduction', p.dateSal as 'Date' from bernalcompany.deductionbonus p where p.dateSal >= '"+AdminReportsForm.getSyear()+"-"+AdminReportsForm.getSmonth()+"-"+AdminReportsForm.getSday()+"' and p.dateSal <= '"+AdminReportsForm.getEyear()+"-"+AdminReportsForm.getEmonth()+"-"+AdminReportsForm.getEday()+"' and ptype = 1 group by idpayroll) as t2,"+
					"(SELECT  idemployee, SUM(HOUR(TIMEDIFF(timeOut,timeIn)))+FLOOR(SUM(MINUTE(TIMEDIFF(timeOut,timeIn)))/60+(SUM(SECOND(TIMEDIFF(timeOut,timeIn)))/60)) as 'WorkHours' from bernalcompany.attendance a where a.dateSal >= '"+AdminReportsForm.getSyear()+"-"+AdminReportsForm.getSmonth()+"-"+AdminReportsForm.getSday()+"' and a.dateSal <= '"+AdminReportsForm.getEyear()+"-"+AdminReportsForm.getEmonth()+"-"+AdminReportsForm.getEday()+"' group by idemployee) as t3,"+
					"(SELECT  idemployee, concat(firstName, ' ', middleInitial, ' ', lastName) as 'Name', baseSalary from bernalcompany.employee e group by idemployee) as t4"+
					" where t1.idpayroll=t2.idpayroll and t1.idpayroll =t3.idemployee and t1.idpayroll=t4.idemployee");
			
			ResultSet res = stat.executeQuery();

			payrollList.getDataVector().removeAllElements();
			tableMod.setAutoCreateRowSorter(true);

			while(res.next()){
				payrollList.addRow(new Object[]{ res.getString("Full Name"), res.getInt("Work Hours"), res.getFloat("Bonus"), res.getFloat("Deduction"), res.getInt("Work Hours")*(res.getFloat("Base Salary")/8), (res.getInt("Work Hours")*(res.getFloat("Base Salary")/8))-res.getFloat("Deduction")+res.getFloat("Bonus")});
				payrollList.fireTableDataChanged();
				totalCost+=(res.getInt("Work Hours")*(res.getFloat("Base Salary")/8))-res.getFloat("Deduction")+res.getFloat("Bonus");
				totalSal+=res.getInt("Work Hours")*(res.getFloat("Base Salary")/8);
				totalDed+=res.getFloat("Deduction");
				totalBon+=res.getFloat("Bonus");
				totalEmp++;
			}

			conn.close();
			
			Connection conn2 = myFactory.getConnection();
			PreparedStatement stat2 = (PreparedStatement) conn2.prepareStatement("SELECT count(DISTINCT(dateSal)) as 'Days', AVG(t3.WorkHours) as 'Hours' from "+
					"(SELECT  idemployee, dateSal, SUM(HOUR(TIMEDIFF(timeOut,timeIn)))+FLOOR(SUM(MINUTE(TIMEDIFF(timeOut,timeIn)))/60+(SUM(SECOND(TIMEDIFF(timeOut,timeIn)))/60)) as 'WorkHours' from bernalcompany.attendance a where a.dateSal >= '"+AdminReportsForm.getSyear()+"-"+AdminReportsForm.getSmonth()+"-"+AdminReportsForm.getSday()+"' and a.dateSal <= '"+
					AdminReportsForm.getEyear()+"-"+AdminReportsForm.getEmonth()+"-"+AdminReportsForm.getEday()+"' group by idemployee) as t3");
			
			ResultSet res2 = stat2.executeQuery();
			
			if(res2.next()){
				numEmp.setText("Number of Employees: " + totalEmp);
				aveHours.setText("Average Working Hours: " + res2.getFloat("Hours"));
				numDays.setText("Number of Working Days: " + res2.getInt("Days"));
				totDed.setText("Total Deductions: " + totalDed);
				totBon.setText("Total Bonuses: " + totalBon);
				totSal.setText("Total Salary: " + totalSal);
				totCost.setText("Total Cost: " + totalCost);
			}
			
			conn2.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}

	}

}